package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Product;
import ModelLayer.Sale;

import ModelLayer.SaleLine;

public class DBSaleLine implements IFDBSaleLine {
	private Connection con;

	public DBSaleLine() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public SaleLine getSaleLine(int saleId, int productId,
			boolean retriveAssociation) {
		String wClause = "  sId = '" + saleId + "'" + " AND pId = '"
				+ productId + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insertSaleLine(SaleLine saleline) throws Exception {
		int rc = -1;
		String query = "INSERT INTO saleline(sId, pId, quantity) VALUES('"
				+ saleline.getSale().getSaleId() + "','"
				+ saleline.getProduct().getProductId() + "','"
				+ saleline.getQuantity() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("Saleline is not inserted");
			throw new Exception("Saleline is not inserted correct");
		}
		return rc;
	}

	@Override
	public ArrayList<SaleLine> getAllSaleLineForSale(int saleId,
			boolean retriveAssociation) {
		// TODO
		String wClause = "  sId = '" + saleId + "'";
		return miscWhere(wClause, retriveAssociation);
	}

	@Override
	public int updateSaleLine(SaleLine saleline) {
		SaleLine salelineObj = saleline;
		int rc = -1;
		String query = "UPDATE saleline SET" + "quantity = '"
				+ saleline.getQuantity() + "'," + " WHERE sId = '"
				+ salelineObj.getSale().getSaleId() + "'" + " AND pId = '"
				+ salelineObj.getProduct().getProductId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in saleline db: " + e);
		}
		return rc;
	}

	private SaleLine singleWhere(String wClause, boolean retriveAssociation) {
		ResultSet results;
		SaleLine saleLine = new SaleLine();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				saleLine = buildSaleLine(results);
				stmt.close();
				if (retriveAssociation) {
					IFDBSale dbSale = new DBSale();
					Sale sale = dbSale.getSale(saleLine.getSale().getSaleId(),
							false);
					saleLine.setSale(sale);
					System.out.println("Sale is selected");

					// get product
					IFDBProduct dbProduct = new DBProduct();
					Product product = dbProduct.getProduct(saleLine
							.getProduct().getProductId(), false);
					saleLine.setProduct(product);
					System.out.println("Product is selected");
				} else {
					saleLine = null;
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return saleLine;
	}

	private ArrayList<SaleLine> miscWhere(String wClause,
			boolean retriveAssociation) {
		ResultSet results;
		ArrayList<SaleLine> list = new ArrayList<SaleLine>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next()) {
				SaleLine saleLine = new SaleLine();
				saleLine = buildSaleLine(results);
				list.add(saleLine);
			}
			stmt.close();
			if (retriveAssociation) {
				for (SaleLine saleLine : list) {
					IFDBSale dbSale = new DBSale();
					Sale sale = dbSale.getSale(saleLine.getSale().getSaleId(),
							false);
					saleLine.setSale(sale);
					System.out.println("Sale is selected");
					// retrive product
					IFDBProduct dbProduct = new DBProduct();
					Product product = dbProduct.getProduct(saleLine
							.getProduct().getProductId(), false);
					saleLine.setProduct(product);
					System.out.println("Product is selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private SaleLine buildSaleLine(ResultSet results) {
		SaleLine saleLine = new SaleLine();
		try {
			saleLine.setQuantity(results.getInt("quantity"));
			saleLine.setSale(new Sale(results.getInt("sId")));
			saleLine.setProduct(new Product(results.getInt("pId")));
		} catch (Exception e) {
			System.out.println("error in building the customer object");
		}
		return saleLine;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT sId, pId,quantity FROM saleline";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}

}
